﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace STAR.Spring.Framework.Module
{
    class DBProcess
    {
        public static DbHelperSQL dbHelper = new DbHelperSQL();

        /// <summary>
        /// 暂时没有调用者
        /// </summary>
        /// <param name="requestName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        internal static DataTable GetDataTable2(string requestName, object[] parms)
        {
            bool isProcExsit = IsProcExsit(requestName);
            StringBuilder sqlTxt = new StringBuilder();

            sqlTxt.AppendFormat("exec {0}", requestName);
            if (parms != null)
            {
                foreach (var item in parms)
                {
                    sqlTxt.AppendFormat("'{0}',", item);
                }
            }

            DataTable dt = dbHelper.ExecuteDataTable(sqlTxt.ToString().TrimEnd(','));
            return dt;
        }

        /// <summary>
        /// 新增数据层返回dataset
        /// 2015-9-9
        /// </summary>
        /// <param name="requestName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        internal static DataSet GetDataSet(string requestName, object[] parms)
        {
            bool isProcExsit = IsProcExsit(requestName);
            if (!isProcExsit)
            {
                return null;
            }

            StringBuilder sqlTxt = new StringBuilder();
            sqlTxt.AppendFormat("exec @requestName", requestName);

            SqlParameter[] sqlParams = null;
            if (parms != null)
            {
                int parmsIndex = 0;

                sqlParams = new SqlParameter[parms.Length + 1];

                foreach (var item in parms)
                {
                    parmsIndex++;
                    string parameterName = string.Format("@parms_{0}", parmsIndex);
                    sqlTxt.AppendFormat("  {0},", parameterName);
                    sqlParams[parmsIndex] = new SqlParameter()
                    {
                        ParameterName = parameterName,
                        //DbType=DbType.String,
                        Value = item
                    };
                }
            }
            else
            {
                sqlParams = new SqlParameter[1];
            }

            sqlParams[0] = new SqlParameter()
            {
                ParameterName = "@requestName",
                DbType=DbType.String,
                Value = requestName
            };

            DataSet ds = dbHelper.ExecuteDataSet(sqlTxt.ToString().TrimEnd(','), CommandType.Text, sqlParams);

            return ds;
        }        
        
        internal static DataTable GetDataTable(string requestName, object[] parms)
        {
            bool isProcExsit = IsProcExsit(requestName);
            if (!isProcExsit)
            {
                return null;
            }

            StringBuilder sqlTxt = new StringBuilder();
            sqlTxt.AppendFormat("exec @requestName", requestName);

            SqlParameter[] sqlParams = null;
            if (parms != null)
            {
                int parmsIndex = 0;

                sqlParams = new SqlParameter[parms.Length + 1];

                foreach (var item in parms)
                {
                    parmsIndex++;
                    string parameterName = string.Format("@parms_{0}", parmsIndex);
                    sqlTxt.AppendFormat("  {0},", parameterName);
                    sqlParams[parmsIndex] = new SqlParameter()
                    {
                        ParameterName = parameterName,
                        //DbType=DbType.String,
                        Value = item
                    };
                }
            }
            else
            {
                sqlParams = new SqlParameter[1];
            }

            sqlParams[0] = new SqlParameter()
            {
                ParameterName = "@requestName",
                DbType=DbType.String,
                Value = requestName
            };

            DataTable dt = dbHelper.ExecuteDataTable(sqlTxt.ToString().TrimEnd(','), CommandType.Text, sqlParams);

            return dt;
        }
      
        private static bool IsProcExsit(string requestName)
        {
            string sqlTxt = @"select * from dbo.sysobjects where id=object_id('@requestName') and objectproperty(id,'isprocedure')=1 ";
            DataTable dt = dbHelper.ExecuteDataTable(sqlTxt, CommandType.Text, new SqlParameter[]
            {
                new SqlParameter()
                {
                     ParameterName="requestName",
                     DbType=DbType.String,
                     Value=requestName
                }
            });
            if (dt == null && dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        internal static DataTable GetDataSet(string requestName, Dictionary<string, string> parms)
        {
            List<SqlParameter> paramList = new List<SqlParameter>();

            foreach (KeyValuePair<string, string> item in parms)
            {
                paramList.Add(new SqlParameter()
                {
                    ParameterName = "@" + item.Key,
                    Value = item.Value,
                    SqlDbType = SqlDbType.NVarChar
                });
            }
            // dbHelper.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
            DataTable dt = dbHelper.ExecuteDataTable(requestName, CommandType.StoredProcedure, paramList.ToArray());
            return dt;
        }
    }
}
